/*
 *this file is part of nseer erp
 *Copyright (C)2006-2010 Nseer(Beijing) Technology co.LTD/http://www.nseer.com 
 *
 *This program is free software; you can redistribute it and/or
 *modify it under the terms of the GNU General Public License
 *as published by the Free Software Foundation; either
 *version 2 of the License, or (at your option) any later version.
 */
package include.query;

import java.sql.*;
import java.util.*;
import filetree.*;
import include.nseer_db.nseer_db;
import include.get_sql.getKeyColumn;

public class NseerSql {

    public String getRegularSql(String dbase, String tablename, String keyword_value, String condition, String queue) {
        getKeyColumn column = new getKeyColumn();
        String sql = "";
        try {
            String[] keyword_column_array = column.Column(dbase, tablename);
            if (!keyword_value.equals("") && keyword_column_array != null && keyword_value.split("⊙").length > 0) {
                sql = "(";
                for (int i = 0; i < keyword_value.split("⊙").length; i++) {
                    for (String option : keyword_column_array) {
                        sql += option + " like " + "'%" + keyword_value.split("⊙")[i] + "%'" + " or ";
                    }
                    sql = sql.substring(0, sql.length() - 4) + ") and (";
                }
                sql = sql.substring(0, sql.length() - 6);
            }
            if (!sql.equals("")) {
                if (!condition.equals("")) {
                    sql += " and " + condition;
                }
            } else {
                if (!condition.equals("")) {
                    sql += " " + condition;
                }
            }
            if (!sql.equals("")) {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + " where " + sql;
                } else {
                    sql = "select * from " + tablename + " where " + sql;
                }
            } else {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + sql;
                } else {
                    sql = "select * from " + tablename;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return sql;
    }

    public String getAdvanceSql(String dbase, String tablename, String condition, String queue, String[] str_select, String[] str_input, String[] time_select, String[] time_inputa, String[] time_inputb, String[] num_select, String[] num_inputa, String[] num_inputb) {
        getKeyColumn column = new getKeyColumn();
        String sql = "";
        try {
            String[] keyword_column_array = column.Column(dbase, tablename);
            for (int i = 0; i < str_select.length; i++) {
                if (!str_select[i].equals("")) {
                    sql += str_select[i] + " like '%" + str_input[i] + "%' and ";
                }
            }
            for (int i = 0; i < time_select.length; i++) {
                if (!time_select[i].equals("")) {
                    if (!time_inputa[i].equals("")) {
                        sql += time_select[i] + " >= '" + time_inputa[i] + " 00:00:00' and ";
                    }
                    if (!time_inputb[i].equals("")) {
                        sql += time_select[i] + " <= '" + time_inputb[i] + " 23:59:59' and ";
                    }
                }
            }
            for (int i = 0; i < num_select.length; i++) {
                if (!num_select[i].equals("")) {
                    if (!num_inputa[i].equals("")) {
                        sql += num_select[i] + " >= '" + num_inputa[i] + "' and ";
                    }
                    if (!num_inputb[i].equals("")) {
                        sql += num_select[i] + " <= '" + num_inputb[i] + "' and ";
                    }
                }
            }
            if (!sql.equals("")) {
                sql = sql.substring(0, sql.length() - 5);
            }
            if (!sql.equals("")) {
                if (!condition.equals("")) {
                    sql += " and " + condition;
                }
            } else {
                if (!condition.equals("")) {
                    sql += " " + condition;
                }
            }
            if (!sql.equals("")) {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + " where " + sql;
                } else {
                    sql = "select * from " + tablename + " where " + sql;
                }
            } else {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + sql;
                } else {
                    sql = "select * from " + tablename;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return sql;
    }

    public String getNotSetSql(String dbase, String tablename, String condition, String queue, String[] str_select) {
        String sql = "";
        try {
            for (int i = 0; i < str_select.length; i++) {
                if (!str_select[i].equals("")) {
                    sql += str_select[i] + " ='' and ";
                }
            }
            if (!sql.equals("")) {
                sql = sql.substring(0, sql.length() - 5);
            }
            if (!sql.equals("")) {
                if (!condition.equals("")) {
                    sql += " and " + condition;
                }
            } else {
                if (!condition.equals("")) {
                    sql += " " + condition;
                }
            }
            if (!sql.equals("")) {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + " where " + sql;
                } else {
                    sql = "select * from " + tablename + " where " + sql;
                }
            } else {
                if (!queue.equals("")) {
                    sql += " " + queue;
                    sql = "select * from " + tablename + sql;
                } else {
                    sql = "select * from " + tablename;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return sql;
    }
}